// https://www.npmjs.com/package/mysql2/v/1.0.0-rc.6
const moment = require('moment/moment');
const mysql = require('mysql2/promise');
//只能通过fs文件操作或者数据库实现数据持久化，常规的模块导入导出在项目重启后修改的数据会被重置
const fs = require('fs');
//大批量数据写入数据库
// 创建数据库连接
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'ace.2020',
    database: 'admin',
    connectionLimit: 100  // 设置连接池的最大连接数
});

// 设置结束时间
const endTime = moment('2024-01-01');
function mockData(data) {

    const recodTime = data.add(1, 'minutes').format("YYYY-MM-DD HH:mm:ss")
    // fft
    const fftValues = Array.from({ length: 4099 }, () => (Math.random() * 30).toFixed(5) + 20).join(",");
    const fftData = { recordTime: recodTime, sample: 48000, band: 8192, data: fftValues };
    //oct
    const octValues = Array.from({ length: 21 }, () => (Math.random() * 10).toFixed(5) + 30).join(",");
    const octData = { recordTime: recodTime, data: octValues };
    //1/3oct
    const oneThirdValues = Array.from({ length: 52 }, () => (Math.random() * 10).toFixed(5) + 30).join(",");
    const oneThirdData = { recordTime: recodTime, data: oneThirdValues };

    //total_noise
    const totalValues = Array.from({ length: 4099 }, () => (Math.random() * 10).toFixed(5) + 20).join(",");
    const totalData = { recordTime: recodTime, data: totalValues };
    return {
        octData,
        oneThirdData,
        totalData,
        fftData
    };

}
// 使用JavaScript的数据库连接池（pool）来获取一个数据库连接，connection参数将包含一个有效的数据库连接对象。会返回一个Promise对像
// const connection =  pool.getConnection((err, connection) => {
//     if (err) {
//         return;
//     }
// })
async function insertData(data) {
    const connection = await pool.getConnection()
    await connection.beginTransaction();

    try {
        const { octData, oneThirdData, totalData, fftData } = mockData(data);
        await connection.query('INSERT INTO 010157_0_and_oct SET ?', [octData]);
        await connection.query('INSERT INTO 010157_0_and_oct_one_third SET ?', [oneThirdData]);
        await connection.query('INSERT INTO 010157_0_and_total_noise SET ?', [totalData]);
        await connection.query('INSERT INTO 010157_0_and_fft SET ?', [fftData]);
        const { octData:a, oneThirdData:b, totalData:c, fftData:d } = mockData(data);
        await connection.query('INSERT INTO 010157_1_and_oct SET ?', [a]);
        await connection.query('INSERT INTO 010157_1_and_oct_one_third SET ?', [b]);
        await connection.query('INSERT INTO 010157_1_and_total_noise SET ?', [c]);
        await connection.query('INSERT INTO 010157_1_and_fft SET ?', [d]);
        await connection.commit();

        console.log('插入成功',data.format('YYYY-MM-DD HH:mm:ss'));
        fs.writeFileSync('data.json', JSON.stringify({data:data.format('YYYY-MM-DD HH:mm:ss')}), 'utf8');

    } catch (err) {
        await connection.rollback();
        console.log('插入失败', err);
    } finally {
        connection.release();
    }
}


async function continuousWrite() {
    // fs读取获取储存的数据
    storedData = JSON.parse(fs.readFileSync('data.json', 'utf8'));
    //data为变化的时间
    data = moment(storedData.data);
    while (data < endTime) {
        let storedData = JSON.parse(fs.readFileSync('data.json', 'utf8'));
        data = moment(storedData.data).add(10, 'minutes');
        await insertData(data)

    }
}
continuousWrite()

//*************** */
// 获取数据
// connection.query(
//     'select * from user', [],
//     function(err, results, fields) {
//         if (err) {
//             console.log('[SELECT ERROR] - ', err.message);
//             return;
//         }
//         console.log(results);
//     }
// )
